﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Kingdee.BOS;
using Kingdee.BOS.App;
using Kingdee.BOS.App.Core;
using Kingdee.BOS.App.Data;
using Kingdee.BOS.Contracts;
using Kingdee.BOS.Core;
using Kingdee.BOS.Core.Bill;
using Kingdee.BOS.Core.DynamicForm;
using Kingdee.BOS.Core.DynamicForm.PlugIn;
using Kingdee.BOS.Core.Metadata;
using Kingdee.BOS.Core.Metadata.FieldElement;
using Kingdee.BOS.Core.Metadata.FormElement;
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS.ServiceHelper;
using Kingdee.BOS.Util;
using Kingdee.K3.Core.SCM.Args;
using Kingdee.K3.SCM.ServiceHelper;

namespace K.KDL
{
    public static class BosHelper
    {
        #region | Fields |

        static IViewService _viewService = ServiceHelper.GetService<IViewService>();

        // Mappers
        static Dictionary<string, string> _settleMap = new Dictionary<string, string>();
        //static Dictionary<string, string> _orgMap = new Dictionary<string, string>();
        static Dictionary<string, string> _currencyMap = new Dictionary<string, string>();
        static Dictionary<string, string> _matchMethodMap = new Dictionary<string, string>();

        static Dictionary<string, int> _orgMap = new Dictionary<string, int>();
        static Dictionary<string, Tuple<int, int>> accountbookMap = new Dictionary<string, Tuple<int, int>>();
        static Dictionary<string, int> supplierMap = new Dictionary<string, int>();
        static Dictionary<string, int> customerMap = new Dictionary<string, int>();
        static Dictionary<string, Tuple<int, int, string>> _auxAttributeMap = new Dictionary<string, Tuple<int, int, string>>();

        #endregion

        #region | Fill Field Data |

        public static void FillBaseDataField(this BusinessInfo metaData, Context context, DynamicObject dataEntity, string fieldName, object id)
        {
            var baseDataField = metaData.GetField(fieldName) as BaseDataField;
            if (baseDataField == null) throw new Exception($"名称为[{fieldName}]的BOS字段无法找到");

            var baseData = _viewService.LoadFromCache(context, new object[] { id }, baseDataField.RefFormDynamicObjectType)[0];
            baseDataField.DynamicProperty.SetValue(dataEntity, baseData);
            baseDataField.RefIDDynamicProperty.SetValue(dataEntity, id);
        }

        public static void FillAssistDataField(this BusinessInfo metaData, Context context, DynamicObject dataEntity, string fieldName, object id)
        {
            var baseDataField = metaData.GetField(fieldName) as AssistantField;
            if (baseDataField == null) throw new Exception($"名称为[{fieldName}]的BOS字段无法找到");

            var baseData = _viewService.LoadFromCache(context, new object[] { id }, baseDataField.RefFormDynamicObjectType)[0];
            baseDataField.DynamicProperty.SetValue(dataEntity, baseData);
            baseDataField.RefIDDynamicProperty.SetValue(dataEntity, id);
        }

        public static void FillFlexField(this BusinessInfo metaData, Context context, DynamicObject dataEntity, string fieldName, object id)
        {
            var field = metaData.GetField(fieldName) as RelatedFlexGroupField;
            if (field == null) throw new Exception($"名称为[{fieldName}]的BOS字段无法找到");

            var value = _viewService.LoadFromCache(context, new object[] { id }, field.RefFormDynamicObjectType)[0];
            field.DynamicProperty.SetValue(dataEntity, value);
            field.RefIDDynamicProperty.SetValue(dataEntity, id);
        }

        public static void FillLotField(this BusinessInfo metaData, Context context, DynamicObject dataEntity, string fieldName, object lotId, string lotText)
        {
            var lotField = metaData.GetField(fieldName) as LotField;
            if (lotField == null) throw new Exception($"名称为[{fieldName}]的BOS字段无法找到");
            var baseData = _viewService.LoadFromCache(context, new object[] { lotId }, lotField.RefFormDynamicObjectType)[0];
            lotField.DynamicProperty.SetValue(dataEntity, baseData);
            lotField.RefIDDynamicProperty.SetValue(dataEntity, lotId);
            lotField.TextDynamicProperty.SetValue(dataEntity, lotText);
        }

        public static void FillAuxAttributeField(this BusinessInfo metaData, Context context, DynamicObject billObj, DynamicObject line, string lineEntryName, string matOrmName, string flexOrmName, string auxPropName, object idValue)
        {
            var flexFieldName = "F" + flexOrmName;
            var matFieldName = "F" + matOrmName;

            var flexField = metaData.GetField(flexFieldName) as RelatedFlexGroupField;
            if (flexField == null) throw new Exception($"名称为[{flexFieldName}]的BOS字段无法找到");

            if (!_auxAttributeMap.Keys.Contains(auxPropName))
            {
                var auxAttributeIds = DBUtils.ExecuteDynamicObject(context, $"SELECT FID, FVALUETYPE, FFLEXNUMBER FROM T_BD_FLEXAUXPROPERTY WHERE FNUMBER='{auxPropName}';");
                if (auxAttributeIds.Count == 0) throw new Exception($"编码为[{auxPropName}]的辅助属性无法找到");
                _auxAttributeMap.Add(auxPropName, new Tuple<int, int, string>((int)auxAttributeIds[0][0], (int)auxAttributeIds[0][1], auxAttributeIds[0][2].ToString()));
            }

            var auxPropId = _auxAttributeMap[auxPropName].Item1;
            var auxPropType = _auxAttributeMap[auxPropName].Item2;
            var auxPropNumber = _auxAttributeMap[auxPropName].Item3;

            var auxObj = line[flexOrmName] as DynamicObject;
            if (auxObj == null)
            {
                var doType = flexField.RefFormDynamicObjectType;
                auxObj = new DynamicObject(doType);
                //auxObj[matOrmName] = line[matFieldName];
                //auxObj[matOrmName + "_Id"] = line[matFieldName + "_Id"];
            }

            var auxPropField = (metaData.GetField("FAuxPropId") as RelatedFlexGroupField).RelateFlexBusinessInfo.Elements.Single(_ => _.Key == auxPropNumber);
            if (auxPropField is TextField)
            {
                auxObj[$"F{auxPropId}"] = idValue;
            }
            else if (auxPropField is BaseDataField)
            {
                var auxPropObj = _viewService.LoadFromCache(context, new object[] { idValue }, (auxPropField as BaseDataField).RefFormDynamicObjectType)[0];
                auxObj[$"F{auxPropId}_Id"] = idValue;
                auxObj[$"F{auxPropId}"] = auxPropObj;
            }
            var flexFieldType =
            line[flexOrmName] = auxObj;

            var fss = new FlexSaveService(context, "Save");
            // 调用辅助属性保存服务：处理规则和框架一致
            fss.BatchSaveEntityFlexObject(metaData, metaData.GetEntity(lineEntryName), new DynamicObject[] { billObj });

            // 获取辅助属性ID
            var auxId = FlexServiceHelper.GetFlexDataId(context, auxObj, FormIdConst.BD_FLEXSITEMDETAILV);
            auxObj["Id"] = auxId;
            line[flexOrmName + "_Id"] = auxId;
        }

        #endregion

        #region | IDs |

        public static StockInfo GetStockInfoViaNumber(this Context context, string number)
        {
            var sql = @"/*dialect*/SELECT S.FSTOCKID, S.FNUMBER, SL.FNAME, S.FUSEORGID
                        FROM T_BD_STOCK S
                        INNER JOIN T_BD_STOCK_L SL ON S.FSTOCKID = SL.FSTOCKID AND SL.FLOCALEID = 2052
                        WHERE FNUMBER = '{0}'";

            var rows = DBUtils.ExecuteDynamicObject(context, string.Format(sql, number));
            if (rows.Count > 0)
            {
                var stockLine = rows[0];
                return new StockInfo
                {
                    Id = (int)stockLine["FSTOCKID"],
                    Numer = number,
                    Name = stockLine["FNAME"].ToString(),
                    OrgId = (int)stockLine["FUSEORGID"],
                };
            }
            else
            {
                throw new Exception($"系统中无法找到编码为[{number}]的仓库！");
            }
        }

        public static string GetSettleNameViaId(this Context context, string id)
        {
            if (!_settleMap.ContainsKey(id))
            {
                var sql = $"/*dialect*/SELECT FNAME FROM T_BD_SETTLETYPE_L WHERE FLOCALEID=2052 AND FID='{id}';";
                var rows = DBUtils.ExecuteDynamicObject(context, sql);
                if (rows.Count > 0)
                {
                    _settleMap.Add(id, rows[0][0].ToString());
                }
                else
                {
                    return string.Empty;
                }
            }
            return _settleMap[id];
        }

        //public static string GetOrgNumberViaId(this Context context, string id)
        //{
        //    if (!_orgMap.ContainsKey(id))
        //    {
        //        var sql = $"/*dialect*/SELECT FNUMBER FROM T_ORG_ORGANIZATIONS WHERE FORGID = '{id}';";
        //        var rows = DBUtils.ExecuteDynamicObject(context, sql);
        //        _orgMap.Add(id, rows[0][0].ToString());
        //    }
        //    return _orgMap[id];
        //}

        public static string GetMatchMethodViaId(this Context context, string id)
        {
            if (!_matchMethodMap.ContainsKey(id))
            {
                var sql = $"/*dialect*/SELECT FNAME, FMATCHMETHODID FROM T_AR_MATCHMETHODMESS_L WHERE FLOCALEID = 2052 AND FMATCHMETHODID = '{id}';";
                var rows = DBUtils.ExecuteDynamicObject(context, sql);
                _matchMethodMap.Add(id, rows[0][0].ToString());
            }
            return _matchMethodMap[id];
        }

        public static string GetCurrencyCodeViaId(this Context context, string id)
        {
            if (!_currencyMap.ContainsKey(id))
            {
                var sql = $"/*dialect*/SELECT FCODE, FCURRENCYID FROM T_BD_CURRENCY WHERE FCURRENCYID = '{id}';";
                var rows = DBUtils.ExecuteDynamicObject(context, sql);
                _currencyMap.Add(id, rows[0][0].ToString());
            }
            return _currencyMap[id];
        }

        public static int GetUserIdViaName(this Context context, string name)
        {
            var sql = $@"SELECT FUSERID FROM T_SEC_USER WHERE FNAME = '{name}';";
            var rows = DBUtils.ExecuteDynamicObject(context, sql);
            if (rows.Count > 0)
            {
                return (int)rows[0][0];
            }
            else
            {
                throw new Exception($"系统中无法用户名为[{name}]的用户！");
            }

        }

        /// ID, DEPTID, GROUPID, SALLER, 地址信息, 价目表ID
        public static int GetCustId(this Context context, string number)
        {
            var sql = @"SELECT FCUSTID, FNUMBER, F_PAEZ_BU FROM DBO.T_BD_CUSTOMER WHERE FNUMBER='{0}';";
            var rows = DBUtils.ExecuteDynamicObject(context, string.Format(sql, number));
            if (rows.Count > 0)
            {
                return (int)rows[0][0];
            }
            else
            {
                throw new Exception($"系统中无法找到编码为[{number}]的客户！");
            }
        }

        public static int GetSuppId(this Context context, string number, int orgId)
        {
            var sql = $"SELECT FSUPPLIERID, FNUMBER FROM DBO.T_BD_SUPPLIER WHERE FNUMBER='{number}' AND FUSEORGID='{orgId}';";
            var rows = DBUtils.ExecuteDynamicObject(context, sql);
            if (rows.Count > 0)
            {
                return (int)rows[0][0];
            }
            else
            {
                throw new Exception($"系统中无法找到编码为[{number}]的供应商！");
            }
        }

        /// ID, DEPTID, GROUPID, SALLER, 地址信息, 价目表ID
        public static CustomerInfo GetCustInfoViaNumber(this Context context, string number)
        {
            var sql = @"SELECT FCUSTID, FNUMBER, F_PAEZ_BU FROM DBO.T_BD_CUSTOMER WHERE FNUMBER='{0}';";
            var rows = DBUtils.ExecuteDynamicObject(context, string.Format(sql, number));
            if (rows.Count > 0)
            {
                return new CustomerInfo
                {
                    Id = (int)rows[0][0],
                    Number = rows[0][1]?.ToString(),
                    BusinessUnit = rows[0][2]?.ToString(),
                };
            }
            else
            {
                throw new Exception($"系统中无法找到编码为[{number}]的客户！");
            }
        }

        public static int GetOrgIdViaNumber(this Context context, string number)
        {
            if (!_orgMap.Keys.Contains(number))
            {
                var sql = string.Format("SELECT FORGID FROM DBO.T_ORG_ORGANIZATIONS WHERE FNUMBER='{0}';", number);
                var rows = DBUtils.ExecuteDynamicObject(context, sql);
                if (rows.Count > 0)
                {
                    _orgMap.Add(number, (int)rows[0][0]);
                }
                else
                {
                    throw new Exception($"系统中无法找到编码为[{number}]的组织机构！");
                }
            }

            return _orgMap[number];
        }

        public static int GetOrgIdViaPlantCode(this Context context, string plantCode)
        {
            if (!_orgMap.Keys.Contains(plantCode))
            {
                var sql = string.Format("SELECT FORGID FROM DBO.T_ORG_ORGANIZATIONS WHERE F_PAEZ_Plant='{0}';", plantCode);
                var rows = DBUtils.ExecuteDynamicObject(context, sql);
                if (rows.Count == 1)
                {
                    _orgMap.Add(plantCode, (int)rows[0][0]);
                }
                else
                {
                    throw new Exception($"系统中无法找到Plant Code为[{plantCode}]的组织机构或者该Plant Code对应的组织机构有多个！");
                }
            }

            return _orgMap[plantCode];
        }

        public static int GetStockIdViaNumber(this Context context, string number, int orgId)
        {
            var sql = $"SELECT FSTOCKID, FNUMBER FROM T_BD_STOCK WHERE FNUMBER = '{number}' AND FUSEORGID = '{orgId}';";
            var rows = DBUtils.ExecuteDynamicObject(context, sql);
            if (rows.Count > 0)
            {
                return (int)rows[0][0];
            }
            else
            {
                throw new Exception($"系统中找不到编码为[{number}]的仓库!");
            }
        }

        public static MatInfo GetMaterialInfoViaNumber(this Context context, string number, int orgId)
        {
            var sql = @"SELECT M.FMATERIALID, ISNULL(T.FTAXRATE,0) AS FTAXRATE, S.FSALEUNITID, S.FSALEPRICEUNITID, STK.FSTOREUNITID, B.FBASEUNITID, STK.FAUXUNITID
                    FROM DBO.T_BD_MATERIAL M
                    INNER JOIN T_BD_MATERIALBASE B ON M.FMATERIALID=B.FMATERIALID
					INNER JOIN T_BD_MATERIALSALE S ON M.FMATERIALID=S.FMATERIALID
                    INNER JOIN T_BD_MATERIALSTOCK STK ON M.FMATERIALID=STK.FMATERIALID
					LEFT JOIN T_BD_TAXRATE T ON B.FTAXRATEID=T.FID
                    WHERE M.FNUMBER='{0}' AND M.FUSEORGID='{1}';";
            var row = DBUtils.ExecuteDynamicObject(context, string.Format(sql, number, orgId))?.FirstOrDefault();
            if (row != null)
            {
                return new MatInfo
                {
                    Id = (int)row["FMATERIALID"],
                    TaxRate = (decimal)row["FTAXRATE"],
                    SalUnitId = (int)row["FSALEUNITID"],
                    PriceUnitId = (int)row["FSALEPRICEUNITID"],
                    StockUnitId = (int)row["FSTOREUNITID"],
                    BaseUnitId = (int)row["FBASEUNITID"],
                    AuxUnitId = (int)row["FAUXUNITID"]
                };
            }
            else
            {
                throw new Exception($"系统中无法找到编码为[{number}]的物料！");
            }
        }

        public static MatInfo GetMaterialInfoViaID(this Context context, string id)
        {
            var sql = @"SELECT M.FMATERIALID, M.FNUMBER, S.FSALEUNITID, S.FSALEPRICEUNITID, STK.FSTOREUNITID, B.FBASEUNITID, STK.FAUXUNITID
                    FROM DBO.T_BD_MATERIAL M
                    INNER JOIN T_BD_MATERIALBASE B ON M.FMATERIALID=B.FMATERIALID
                    INNER JOIN T_BD_MATERIALSALE S ON M.FMATERIALID=S.FMATERIALID
                    INNER JOIN T_BD_MATERIALSTOCK STK ON M.FMATERIALID=STK.FMATERIALID
                    WHERE M.FMATERIALID='{0}';";
            var rows = DBUtils.ExecuteDynamicObject(context, string.Format(sql, id));
            if (rows.Count > 0)
            {
                return new MatInfo
                {
                    Id = (int)rows[0][0],
                    Number = rows[0][1].ToString(),
                    SalUnitId = (int)rows[0][2],
                    PriceUnitId = (int)rows[0][3],
                    StockUnitId = (int)rows[0][4],
                    BaseUnitId = (int)rows[0][5],
                    AuxUnitId = (int)rows[0]["FAUXUNITID"]
                };
            }
            else
            {
                throw new Exception($"系统中无法找到内码为[{id}]的物料！");
            }
        }

        public static string GetAssistItemIdViaName(this Context context, string assistName, string itemName)
        {
            var sql = @"SELECT EL.FENTRYID FROM T_BAS_ASSISTANTDATA_L L
                        INNER JOIN T_BAS_ASSISTANTDATAENTRY E ON L.FID=E.FID
                        INNER JOIN T_BAS_ASSISTANTDATAENTRY_L EL ON E.FENTRYID=EL.FENTRYID AND EL.FLOCALEID=2052
                        WHERE L.FLOCALEID =2052 AND L.FNAME='{0}'
                        AND EL.FDATAVALUE='{1}';";
            var rows = DBUtils.ExecuteDynamicObject(context, string.Format(sql, assistName, itemName));
            if (rows.Count > 0)
            {
                return rows[0][0].ToString();
            }
            else
            {
                return null;
            }
        }

        public static string GetAssistItemIdViaNumber(this Context context, string assistName, string number)
        {
            var sql = @"SELECT E.FENTRYID FROM T_BAS_ASSISTANTDATA_L L
                        INNER JOIN T_BAS_ASSISTANTDATAENTRY E ON L.FID=E.FID
                        WHERE L.FLOCALEID =2052 AND L.FNAME='{0}'
                        AND E.FNUMBER='{1}';";
            var rows = DBUtils.ExecuteDynamicObject(context, string.Format(sql, assistName, number));
            if (rows.Count > 0)
            {
                return rows[0][0].ToString();
            }
            else
            {
                return null;
            }
        }

        public static Tuple<int, string> GetSettleTypeIdViaNumber(this Context context, string number)
        {
            var sql = @"SELECT FID, FTYPE, FNUMBER FROM T_BD_SETTLETYPE WHERE FNUMBER='{0}';";
            var rows = DBUtils.ExecuteDynamicObject(context, string.Format(sql, number));
            if (rows.Count > 0)
            {
                return new Tuple<int, string>((int)rows[0][0], rows[0][1].ToString());
            }
            else
            {
                return new Tuple<int, string>(-1, "");
            }
        }

        // 部门
        public static int GetDeptIdViaNumber(this Context context, string name)
        {
            var sql = @"SELECT FDEPTID, FNUMBER FROM DBO.T_BD_DEPARTMENT WHERE FNUMBER = '{0}';";
            var rows = DBUtils.ExecuteDynamicObject(context, string.Format(sql, name));
            if (rows.Count > 0)
            {
                return (int)rows[0][0];
            }
            else
            {
                throw new Exception($"系统中无法找到编码为[{name}]的部门");
            }
        }

        public static int GetEmployeeIdViaNumber(this Context context, string number)
        {
            var sql = $"SELECT fid FROM T_HR_EMPINFO WHERE FNUMBER = '{number}';";
            var rows = DBUtils.ExecuteDynamicObject(context, sql);
            if (rows.Count > 0)
            {
                return (int)rows[0][0];
            }
            else
            {
                throw new Exception($"系统中无法找到编码为[{number}]的员工");
            }
        }

        public static Tuple<int, int> GetEmployeeInfoViaNumber(this Context context, string number)
        {
            var sql = $"SELECT fid, FDEPTID FROM T_HR_EMPINFO WHERE FNUMBER = '{number}';";
            var rows = DBUtils.ExecuteDynamicObject(context, sql);
            if (rows.Count > 0)
            {
                return new Tuple<int, int>((int)rows[0][0], (int)rows[0][1]);
            }
            else
            {
                throw new Exception($"系统中无法找到编码为[{number}]的员工");
            }
        }

        public static int GetUnitIdViaNumber(this Context context, string number)
        {
            var sql = string.Format("SELECT FUNITID FROM T_BD_UNIT WHERE FNUMBER='{0}';", number);
            var rows = DBUtils.ExecuteDynamicObject(context, sql);
            if (rows.Count > 0)
            {
                return (int)rows[0][0];
            }
            else
            {
                throw new Exception($"系统中无法找到编码为[{number}]的单位");
            }
        }

        public static int GetUnitIdViaName(this Context context, string name)
        {
            var sql = string.Format("SELECT FUNITID FROM T_BD_UNIT_L WHERE FNAME='{0}' AND FLOCALEID=2052;", name);
            var rows = DBUtils.ExecuteDynamicObject(context, sql);
            if (rows.Count > 0)
            {
                return (int)rows[0][0];
            }
            else
            {
                throw new Exception($"系统中无法找到名称为[{name}]的单位");
            }
        }

        public static Tuple<int, string, int, string, int> GetLocInfoViaNumber(this Context context, string number, int custId)
        {
            var sql = $"SELECT FENTRYID, FADDRESS, FTCONTACT, F_PAEZ_CENTERY, F_PAEZ_CANGKU1 FROM T_BD_CUSTLOCATION WHERE FCUSTID='{custId}' AND FNUMBER='{number}' AND FISUSED='1';";
            var rows = DBUtils.ExecuteDynamicObject(context, sql);
            if (rows.Count > 0)
            {
                return new Tuple<int, string, int, string, int>((int)rows[0][0], rows[0][1]?.ToString(), (int)rows[0][2], rows[0][3]?.ToString(), (int)rows[0][4]);
            }
            else
            {
                throw new Exception($"系统中无法找到编码为[{number}]的客户地址");
            }
        }

        public static int GetLotIdViaNumber(this Context context, string number, string matNumber)
        {
            var sql = $@"SELECT FLOTID FROM T_BD_LOTMASTER LM
                INNER JOIN T_BD_MATERIAL M ON LM.FMATERIALID = M.FMASTERID
                WHERE FLOTSTATUS!= 0 AND FBIZTYPE = '1'
                AND LM.FNUMBER = '{number}'
                AND M.FMATERIALID = '{matNumber}';";
            var rows = DBUtils.ExecuteDynamicObject(context, sql);
            if (rows.Count > 0)
            {
                return (int)rows[0][0];
            }
            else
            {
                throw new Exception($"系统中找不到编码为[{number}]且状态不为[未生效]、业务类型为[库存]的批号主档");
            }
        }

        public static List<Tuple<DateTime, DateTime, decimal>> GetProductDateInfo(this Context context, int lotId, int stockId, int materialId)
        {
            var result = new List<Tuple<DateTime, DateTime, decimal>>();
            if (lotId == 0)
            {
                return result;
            }

            var materialSQL = $@"SELECT M.FMATERIALID, M.FMASTERID, S.FISKFPERIOD, S.FISEXPPARTOFLOT
                    FROM T_BD_MATERIAL M
                    INNER JOIN T_BD_MATERIALSTOCK S ON M.FMATERIALID=S.FMATERIALID
                    WHERE M.FMATERIALID='{materialId}';";
            var materialRows = DBUtils.ExecuteDynamicObject(context, materialSQL);
            if (materialRows.Count == 0)
            {
                return result;
            }
            var materialInfo = materialRows[0];

            // 是否启用保质期管理
            if (materialInfo["FISKFPERIOD"]?.ToString() == "0")
            {
                return result;
            }

            var lotSQL = $@"SELECT FLOTID, FNUMBER, FUSEORGID, FPRODUCEDATE, FEXPIRYDATE FROM T_BD_LOTMASTER where FLOTID='{lotId}';";
            var lotRows = DBUtils.ExecuteDynamicObject(context, lotSQL);
            if (lotRows.Count == 0)
            {
                return result;
            }
            var lotInfo = lotRows[0];

            var materialMasterId = Convert.ToInt64(materialInfo["FMASTERID"]);
            var orgId = Convert.ToInt64(lotInfo["FUSEORGID"]);

            if (materialInfo["FISEXPPARTOFLOT"]?.ToString() == "0")
            {
                var lotInvExpiryInfoSQL = $@"SELECT T1.FPRODUCEDATE AS FPRODUCEDATE,T1.FEXPIRYDATE AS FEXPIRYDATE, T1.FBASEQTY
                                    FROM T_STK_INVENTORY T1
                                    WHERE 
                                    T1.FMATERIALID = '{materialMasterId}' 
                                    AND T1.FLOT = '{lotId}'
                                    AND T1.FSTOCKORGID = '{orgId}' 
                                    AND T1.FSTOCKID='{stockId}'
                                    AND FBASEQTY > 0
                                    AND FISEFFECTIVED = 1
                                    ORDER BY T1.FPRODUCEDATE";

                var lotInvExpiryInfoRows = DBUtils.ExecuteDynamicObject(context, lotInvExpiryInfoSQL);
                if (lotInvExpiryInfoRows != null && lotInvExpiryInfoRows.Count > 0)
                {
                    foreach (var item in lotInvExpiryInfoRows)
                    {
                        if (item["FPRODUCEDATE"] is DBNull || item["FEXPIRYDATE"] is DBNull) continue;
                        result.Add(new Tuple<DateTime, DateTime, decimal>((DateTime)item["FPRODUCEDATE"], (DateTime)item["FEXPIRYDATE"], (decimal)item["FBASEQTY"]));
                    }
                    return result;
                }
            }
            else
            {
                result.Add(new Tuple<DateTime, DateTime, decimal>((DateTime)lotInfo["FPRODUCEDATE"], (DateTime)lotInfo["FEXPIRYDATE"], 0));
                return result;
            }

            return result;
        }

        #endregion

        #region | EXCEL ROW |

        public static decimal GetDecimalValue(this object rawVaue)
        {
            if (rawVaue == null || string.IsNullOrWhiteSpace(rawVaue.ToString()))
            {
                return 0;
            }
            else return decimal.Parse(rawVaue.ToString().Trim());
        }

        public static int GetIntValue(this object rawVaue)
        {
            if (rawVaue == null || string.IsNullOrWhiteSpace(rawVaue.ToString()))
            {
                return 0;
            }
            else return int.Parse(rawVaue.ToString().Trim());
        }

        public static string GetStringValue(this object rawVaue, int maxLength = 0)
        {
            if (rawVaue == null)
            {
                return string.Empty;
            }
            else
            {
                var value = rawVaue.ToString();
                if (maxLength == 0)
                {
                    return value;
                }
                else if (value.Length >= maxLength)
                {
                    return value.Substring(0, maxLength);
                }
                else
                {
                    return value.Substring(0, value.Length);
                }
            }
        }

        public static DateTime GetDateValue(this object rawVaue)
        {
            var dateTime = DateTime.MinValue;
            double oaTimeValue;
            if (rawVaue == null)
            {
                dateTime = DateTime.MinValue;
            }
            else if (!double.TryParse(rawVaue.ToString(), out oaTimeValue))
            {
                DateTime.TryParse(rawVaue.ToString().Trim(), out dateTime);
            }
            else dateTime = DateTime.FromOADate(oaTimeValue);

            return dateTime;
        }

        #endregion

        #region | View |

        public static IBillView CreateBillView(this Context ctx, string formId)
        {
            var formMetadata = MetaDataServiceHelper.Load(ctx, formId, true) as FormMetadata;
            if (formMetadata == null)
            {
                return null;
            }
            Form form = formMetadata.BusinessInfo.GetForm();
            Type type = Type.GetType("Kingdee.BOS.Web.Import.ImportBillView,Kingdee.BOS.Web");
            if (type == (Type)null)
            {
                return null;
            }
            IDynamicFormViewService dynamicFormViewService = (IDynamicFormViewService)Activator.CreateInstance(type);
            BillOpenParameter param = CreateOpenParameter(ctx, formMetadata);
            IResourceServiceProvider formServiceProvider = form.GetFormServiceProvider(false);
            dynamicFormViewService.Initialize(param, formServiceProvider);
            return dynamicFormViewService as IBillView;
        }

        private static BillOpenParameter CreateOpenParameter(Context ctx, FormMetadata metaData)
        {
            Form form = metaData.BusinessInfo.GetForm();
            BillOpenParameter billOpenParameter = new BillOpenParameter(form.Id, metaData.GetLayoutInfo().Id);
            billOpenParameter.Context = ctx;
            billOpenParameter.ServiceName = form.FormServiceName;
            billOpenParameter.PageId = Guid.NewGuid().ToString();
            billOpenParameter.FormMetaData = metaData;
            billOpenParameter.Status = OperationStatus.ADDNEW;
            billOpenParameter.PkValue = null;
            billOpenParameter.CreateFrom = CreateFrom.Push;
            billOpenParameter.GroupId = "";
            billOpenParameter.ParentId = 0;
            billOpenParameter.DefaultBillTypeId = "";
            billOpenParameter.DefaultBusinessFlowId = "";
            billOpenParameter.SetCustomParameter("PlugIns", form.CreateFormPlugIns());
            billOpenParameter.SetCustomParameter("ShowConfirmDialogWhenChangeOrg", false);
            List<AbstractDynamicFormPlugIn> value = form.CreateFormPlugIns();
            billOpenParameter.SetCustomParameter(FormConst.PlugIns, value);

            return billOpenParameter;
        }

        #endregion

        #region | DataTable Report |


        public static string GetCreateTableSqlFromDT(this DataTable dt)
        {
            return SqlTableCreator.GetCreateFromDataTableSQL(dt.TableName, dt);
        }

        public static DataColumn AddColumn<T>(this DataTable dt, string name)
        {
            var col = dt.Columns.Add(name);
            col.DataType = typeof(T);
            col.DefaultValue = default(T);
            return col;
        }

        public static DataColumn AddColumn(this DataTable dt, string name, int maxLength)
        {
            var col = dt.Columns.Add(name);
            col.DataType = typeof(string);
            col.DefaultValue = string.Empty;
            col.MaxLength = maxLength;
            return col;
        }

        public static decimal ToExpectDecimal(this string value)
        {
            decimal result = 0;
            decimal.TryParse(value, out result);

            return result;
        }

        public static string ToSheetDate(this DateTime date)
        {
            return date.ToString("yyyy-MM-dd HH:mm:ss");
        }

        public static string ToSheetDate(this object date)
        {
            return ((DateTime)date).ToString("yyyy-MM-dd HH:mm:ss");
        }

        public static string ToSDate(this DateTime date)
        {
            return date.ToString("yyyy-MM-dd");
        }

        public static string ToSDate(this object date)
        {
            return ((DateTime)date).ToString("yyyy-MM-dd");
        }

        #endregion
    }

    public class SqlTableCreator
    {
        public static string GetCreateSQL(string tableName, DataTable schema, int[] primaryKeys)
        {

            string sql = "CREATE TABLE " + tableName + " (\n";

            foreach (DataRow column in schema.Rows)
            {

                if (!(schema.Columns.Contains("IsHidden") && (bool)column["IsHidden"]))

                    sql += column["ColumnName"].ToString() + " " + SQLGetType(column) + ",\n";

            }

            sql = sql.TrimEnd(new char[] { ',', '\n' }) + "\n";

            // primary keys
            string pk = "CONSTRAINT PK_" + tableName + " PRIMARY KEY CLUSTERED (";

            bool hasKeys = (primaryKeys != null && primaryKeys.Length > 0);
            if (hasKeys)
            {
                // user defined keys
                foreach (int key in primaryKeys)
                {
                    pk += schema.Rows[key]["ColumnName"].ToString() + ", ";
                }

            }
            else
            {
                // check schema for keys
                string keys = string.Join(", ", GetPrimaryKeys(schema));
                pk += keys;
                hasKeys = keys.Length > 0;
            }

            pk = pk.TrimEnd(new char[] { ',', ' ', '\n' }) + ")\n";

            if (hasKeys) sql += pk;

            sql += ")";

            return sql;
        }

        public static string GetCreateFromDataTableSQL(string tableName, DataTable table)
        {
            string sql = "/*dialect*/CREATE TABLE [" + tableName + "] (\n";

            // columns
            foreach (DataColumn column in table.Columns)
            {
                sql += "[" + column.ColumnName + "] " + SQLGetType(column) + ",\n";
            }

            sql = sql.TrimEnd(new char[] { ',', '\n' }) + "\n";

            // primary keys
            if (table.PrimaryKey.Length > 0)
            {
                sql += "CONSTRAINT [PK_" + tableName + "] PRIMARY KEY CLUSTERED (";

                foreach (DataColumn column in table.PrimaryKey)
                {

                    sql += "[" + column.ColumnName + "],";

                }
                sql = sql.TrimEnd(new char[] { ',' }) + "))\n";
            }
            else
            {
                sql = sql.TrimEnd(new char[] { ',' }) + ")\n";
            }

            return sql;
        }

        public static string[] GetPrimaryKeys(DataTable schema)
        {
            List<string> keys = new List<string>();

            foreach (DataRow column in schema.Rows)
            {
                if (schema.Columns.Contains("IsKey") && (bool)column["IsKey"])
                    keys.Add(column["ColumnName"].ToString());
            }

            return keys.ToArray();
        }

        // Return T-SQL data type definition, based on schema definition for a column
        public static string SQLGetType(object type, int columnSize, int numericPrecision, int numericScale)
        {
            switch (type.ToString())
            {
                case "System.String":
                    return "NVARCHAR(" + ((columnSize == -1) ? 255 : columnSize) + ")";
                case "System.Decimal":
                    if (numericScale > 0)
                        return "DECIMAL";
                    else if (numericPrecision > 10)
                        return "BIGINT";
                    else
                        return "INT";
                case "System.Double":
                case "System.Single":
                    return "REAL";
                case "System.Int64":
                    return "BIGINT";
                case "System.Int16":
                case "System.Int32":
                    return "INT";
                case "System.DateTime":
                    return "DATETIME";
                default:
                    throw new Exception(type.ToString() + " not implemented.");
            }
        }

        public static string SQLGetType(DataRow schemaRow)
        {
            return SQLGetType(schemaRow["DataType"],
                    int.Parse(schemaRow["ColumnSize"].ToString()),
                    int.Parse(schemaRow["NumericPrecision"].ToString()),
                    int.Parse(schemaRow["NumericScale"].ToString()));
        }

        // Overload based on DataColumn from DataTable type
        public static string SQLGetType(DataColumn column)
        {
            return SQLGetType(column.DataType, column.MaxLength, 23, 10);
        }
    }

    public class MatInfo
    {
        public int Id { get; set; }
        public string Number { get; set; }
        public int SalUnitId { get; set; }
        public int PriceUnitId { get; set; }
        public int StockUnitId { get; set; }
        public int BaseUnitId { get; set; }
        public int AuxUnitId { get; set; }
        public decimal TaxRate { get; set; }
    }

    public class CustomerInfo
    {
        public int Id { get; set; }
        public string Number { get; set; }
        public string BusinessUnit { get; set; }
    }

    public class StockInfo
    {
        public int Id { get; set; }
        public string Numer { get; set; }
        public string Name { get; set; }
        public int OrgId { get; set; }
    }

    
}